Mega Code Archive - MSSQL Tutorial

System Settings
1) @@CONNECTIONS Returns the number of connections made to the SQL Server instance since it was last started
2) @@CPU_BUSY Shows the number of busy CPU milliseconds since the SQL Server instance was last started
3) @@error is
4) @@ERROR reset
5) @@IDLE Displays the total idle time of the SQL Server instance in milliseconds, since the instance was last
6) @@IO_BUSY Displays the number of milliseconds spent performing IO operations since the SQL Server instance
7) @@PACK_RECEIVED Returns the total input packets read from the network since the SQL Server instance was last
8) @@PACK_SENT Returns the total output packets sent to the network since the SQL Server instance was last started
9) @@PACKET_ERRORS Displays the total network packet errors that have occurred since the SQL Server instance was
10) @@rowcount can be used to verify the success of selected operations
11) @@SERVICENAME is the Windows service used to execute and maintain the current instance of SQL Server
12) @@TIMETICKS Displays the number of microseconds per tick A tick is a unit of measurement designated by a
13) @@TOTAL_ERRORS is the total number of errors
14) @@TOTAL_READ is a count of the total read operations that have occurred since the current connection was opened
15) @@TOTAL_WRITE Displays the number of disk writes by the SQL Server instance since it was last started
16) @@VERSION VersionInformation
17) A script that creates a summary of the tables in a database
18) A stored procedure that uses the @@ERROR system function
19) Adds the account column to the authors table and creates the new bank and brokerage tables
20) Attempt to Capture @@IDENTITY, @@ROWCOUNT, and @@ERROR
21) Check existance of a view by using OBJECT_ID
22) Check the @@ERROR with if statement
23) Check the view existance
24) Check with OBJECTPROPERTY function
25) Clearing Out Memory
26) Code that tests for the existence of a temporary table
27) Connection-level exception
28) DBCC SHOW STATISTICS (tablename, index_name)
29) DBCC TRACEON
30) Displaying the Nesting Level for the Current Stored Procedure Context
31) Displaying the Oldest Active Transaction with DBCC OPENTRAN and Viewing Lock Activity
32) Dynamically building the DBCC command
33) ERROR_NUMBER functions
34) ERROR_SEVERITY function
35) EXEC and @@ROWCOUNT
36) EXEC master xp_msver
37) Flushing the Procedure Cache
38) Granting PUBLIC permissions will cause you grief
39) HashBytes function
40) HOST_NAME and HOST_ID functions give information about which workstation a particular login has come from
41) If @@ROWCOUNT = 0, return
42) If @@ROWCOUNT 1, rollback
43) Information_schema tables
44) Information_schema views
45) Match a table name in tempdb INFORMATION_SCHEMA TABLES
46) Modifying exception types with XACT_ABORT
47) OBJECT_NAME function
48) Observing line numbers in exceptions
49) Obtain metadata about database objects
50) Output the contents of the root directory of the server to the console
51) Parse exception
52) Query tempdb INFORMATION_SCHEMA TABLES
53) Reseeding the IDENTITY Value
54) Retrieving the Rows Affected by the Previous Statement
55) Returning Information about User-Defined Functions
56) Returning the Current Connections Session ID (SPID)
57) Returning the Current Database ID and Name
58) Returning the Current SQL Server Instance Name and SQL Server Version
59) SELECT 5 0
60) SELECT FROM master dbo sysmessages WHERE error = @@ERROR
61) Select HOST_ID() as Host ID
62) SELECT object_id(employee)
63) SELECT SERVERPROPERTY (Collation)
64) Select SERVERPROPERTY (Edition)
65) Select SESSION_USER
66) SELECT SESSIONPROPERTY (ANSI_PADDING) ANSI_PADDING
67) SELECT SESSIONPROPERTY (ANSI_WARNINGS) ANSI_WARNINGS
68) SELECT SESSIONPROPERTY (ARITHABORT) ARITHABORT
69) SELECT SESSIONPROPERTY (CONCAT_NULL_YIELDS_NULL) CONCAT_NULL_YIELDS_NULL
70) SELECT SESSIONPROPERTY (NUMERIC_ROUNDABORT) NUMERIC_ROUNDABO
71) SELECT SESSIONPROPERTY (QUOTED_IDENTIFIER) QUOTED_IDENTIFIER
72) SELECT TABLE_CATALOG FROM INFORMATION_SCHEMA TABLES WHERE TABLE_NAME = TestTable
73) Select top 10 from Information_schema columns
74) Sends an email when an insert or delete occurs on the discounts table
75) SET ANSI_NULLS ONOFF
76) SET QUOTED_IDENTIFIER ON
77) Show Join hints for INNER HASH JOIN
78) SUSER_NAME Function returns the name of the user logged into the server
79) Syntax for Reseeding the IDENTITY Value with a New Explicit Value
80) Syntax for Retrieving the Current IDENTITY Value of a Table and the Correct Value
81) System views INFORMATION_SCHEMA
82) The syntax of the OBJECT_ID function
83) This variable contains the complete version information for the current instance of SQL Server
84) Updating an end users expiration date
85) Use OBJECT_ID in insert statement
86) Use OBJECTPROPERTY and OBJECT_ID to check the table existance
87) Use SUSER_ID() as constraint
88) Using @@ERROR
89) Using DBCC CHECKIDENT to View and Correct IDENTITY Seed Values
90) Using Join Hints
91) Using SET LOCK TIMEOUT
92) Using SET NOCOUNT OFF to suppress count messages
93) Using the OBJECTPROPERTY Function to Check Whether a View is Indexable
94) Using the SET FORCEPLAN statement
95) Using the SET ROWCOUNT Option
96) Viewing and Setting Current Connection Lock Timeout Settings
97) Viewing the Language Used in the Current Session
98) Viewing User Connection Options
99) XACT_ABORT and transactions
100) XACT_ABORT in a stored procedure
101) Xp_msver function